import datetime
import json

import cx_Oracle

"""使用class多实例"""


class oracle_util_class:

    def __init__(self):
        self.user = "pigpos"
        self.passwd = "P1g#2023pos"
        self.listener = None
        print('构造函数')

    def set_schema(self, schema):
        if schema == 'CTCNZQF':
            self.listener = 'CTCNZQF.cpchina.cn/CTCNZQF'
        elif schema == 'CPGCNXL':
            self.listener = 'CPGCNXL.cpchina.cn/CPGCNXL'
        elif schema == 'NMDCFARM':
            self.listener = '10.240.24.135/NMDCFARM'
        elif schema == 'CRDCFARM':
            self.listener = '10.240.24.135/CRDCFARM'

    def get_connection(self, schema):
        self.set_schema(schema)
        conn = cx_Oracle.connect(self.user, self.passwd, self.listener)
        return conn

    def select(self, sql, schema) -> json:
        self.set_schema(schema)
        conn = cx_Oracle.connect(self.user, self.passwd, self.listener)
        cursor = conn.cursor()
        rows = []
        cursor.execute(sql)
        result = cursor.fetchall()
        col_name = cursor.description
        for row in result:
            d = {}
            for col in range(len(col_name)):
                key = col_name[col][0]
                value = row[col]
                if isinstance(value, datetime.datetime):
                    value = value.strftime('%Y-%m-%d %H:%M:%S')
                d[key] = value
            rows.append(d)
        js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
        cursor.close()
        conn.close()
        return js

    def select2(self, sql, cursor) -> json:
        rows = []
        cursor.execute(sql)
        result = cursor.fetchall()
        col_name = cursor.description
        for row in result:
            d = {}
            for col in range(len(col_name)):
                key = col_name[col][0]
                value = row[col]
                if isinstance(value, datetime.datetime):
                    value = value.strftime('%Y-%m-%d %H:%M:%S')
                d[key] = value
            rows.append(d)
        js = json.dumps(rows, sort_keys=True, ensure_ascii=False, separators=(',', ':'))
        return js
